package com.hzncc.scaffold.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.hzncc.scaffold.entity.Other.Paperrecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hzncc.scaffold.entity.Bo.CertificatesRecordListBo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * <p>
 * 试卷记录表 Mapper 接口
 * </p>
 *
 * @author DrunkBear
 * @since 2022-04-27
 */
@Component
public interface PaperrecordMapper extends BaseMapper<Paperrecord> {

    @Select("SELECT\n" +
            "\t`paperrecord`.*,\n" +
            "\t`certificate`.`id` AS `certificateId`,\n" +
            "\t`certificate`.`imgUrl` AS `certificateImgUrl`,\n" +
            "\t`certificate`.`certNum` AS `certificateCertNum`,\n" +
            "\t`course->chapters`.`id` AS `courseChaptersId`,\n" +
            "\t`course->chapters->tasks`.`id` AS `courseChaptersTasksId`,\n" +
            "\t`course->chapters->tasks`.`title` AS `courseChaptersTasksTitle`,\n" +
            "\t`course->chapters->tasks`.`examType` AS `courseChaptersTasksExamType` \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`paperrecord`.`id`,\n" +
            "\t\t`paperrecord`.`theoryScore`,\n" +
            "\t\t`paperrecord`.`operationScore`,\n" +
            "\t\t`paperrecord`.`averageScore`,\n" +
            "\t\t`paperrecord`.`passed`,\n" +
            "\t\t`paperrecord`.`createTime`,\n" +
            "\t\t`paperrecord`.`resetted`,\n" +
            "\t\t`owner`.`id` AS `ownerId`,\n" +
            "\t\t`owner`.`name` AS `ownerName`,\n" +
            "\t\t`owner`.`realname` AS `ownerRealname`,\n" +
            "\t\t`owner`.`gender` AS `ownerGender`,\n" +
            "\t\t`owner`.`account` AS `ownerAccount`,\n" +
            "\t\t`owner`.`idCardNum` AS `ownerIdCardNum`,\n" +
            "\t\t`owner`.`instituteName` AS `ownerInstituteName`,\n" +
            "\t\t`owner`.`telephone` AS `ownerTelephone`,\n" +
            "\t\t`owner`.`photo` AS `photo`,\n" +
            "\t\t`course`.`id` AS `courseIds`,\n" +
            "\t\t`course`.`title` AS `courseTitle`,\n" +
            "\t\t`course`.`pubTime` AS `pubTime`,\n" +
            "\t\t`course`.`sortTime` AS `sortTime`,\n" +
            "\t\t`course`.`certTemplate` AS `certTemplate`,\n" +
            "\t\t`course`.`setWidth` AS `setWidth`,\n" +
            "\t\t`course`.`setHeight` AS `setHeight`,\n" +
            "\t\t`course`.`coordinate` AS `courseCoordinate` \n" +
            "\tFROM\n" +
            "\t\t`paperrecord` AS `paperrecord`\n" +
            "\t\tINNER JOIN `user` AS `owner` ON `paperrecord`.`userId` = `owner`.`id`\n" +
            "\t\tLEFT OUTER JOIN `course` AS `course` ON `paperrecord`.`courseId` = `course`.`id`  \n" +
            "\tORDER BY\n" +
            "\t\t`paperrecord`.`createTime` desc,\n" +
            "\t\t`paperrecord`.`id` ASC LIMIT 999999 \n" +
            "\t) AS `paperrecord`\n" +
            "\tLEFT OUTER JOIN `certificate` AS `certificate` ON `paperrecord`.`id` = `certificate`.`recordId` \n" +
            "\tAND `certificate`.`isDelete` = 0 \n" +
            "\tAND `certificate`.`belongTo` = 0\n" +
            "\tINNER JOIN `chapter` AS `course->chapters` ON `courseIds` = `course->chapters`.`courseId`\n" +
            "\tINNER JOIN `task` AS `course->chapters->tasks` ON `course->chapters`.`id` = `course->chapters->tasks`.`chapterId` \n" +
            "\tAND `course->chapters->tasks`.`type` = 3 ${ew.customSqlSegment}")
    public List<CertificatesRecordListBo> recordList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT\n" +
            "\t`paperrecord`.*,\n" +
            "\t`certificate`.`id` AS `certificateId`,\n" +
            "\t`certificate`.`imgUrl` AS `certificateImgUrl`,\n" +
            "\t`certificate`.`certNum` AS `certificateCertNum`\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`paperrecord`.`id`,\n" +
            "\t\t`paperrecord`.`theoryScore`,\n" +
            "\t\t`paperrecord`.`operationScore`,\n" +
            "\t\t`paperrecord`.`averageScore`,\n" +
            "\t\t`paperrecord`.`passed`,\n" +
            "\t\t`paperrecord`.`createTime`,\n" +
            "\t\t`paperrecord`.`resetted`,\n" +
            "\t\t`paperrecord`.`classId`,\n" +
            "\t\t`owner`.`id` AS `ownerId`,\n" +
            "\t\t`owner`.`name` AS `ownerName`,\n" +
            "\t\t`owner`.`realname` AS `ownerRealname`,\n" +
            "\t\t`owner`.`gender` AS `ownerGender`,\n" +
            "\t\t`owner`.`account` AS `ownerAccount`,\n" +
            "\t\t`owner`.`idCardNum` AS `ownerIdCardNum`,\n" +
            "\t\t`owner`.`instituteName` AS `ownerInstituteName`,\n" +
            "\t\t`owner`.`telephone` AS `ownerTelephone`,\n" +
            "\t\t`owner`.`photo` AS `photo`,\n" +
            "\t\t`tra`.`id` AS `courseIds`,\n" +
            "\t\t`news`.`title` AS `courseTitle`,\n" +
            "\t\t`tra`.`certTemplate` AS `certTemplate`,\n" +
            "\t\t`tra`.`startTime` AS `pubTime`,\n" +
            "\t\t`tra`.`endTime` AS `sortTime`,\n" +
            "\t\t`tra`.`setWidth` AS `setWidth`,\n" +
            "\t\t`tra`.`setHeight` AS `setHeight`,\n" +
            "\t\t`tra`.`coordinate` AS `courseCoordinate` \n" +
            "\tFROM\n" +
            "\t\t`trainingclass` AS `tra`\n" +
            "\t\tLEFT JOIN `news` AS `news` ON `news`.`id` = `tra`.`newsId`\n" +
            "\t\tLEFT JOIN paperrecord AS paperrecord ON tra.id = paperrecord.classId\n" +
            "\t\tINNER JOIN `user` AS `owner` ON `paperrecord`.`userId` = `owner`.`id` \n" +
            "\tORDER BY\n" +
            "\t\t`paperrecord`.`createTime` desc,\n" +
            "\t\t`paperrecord`.`id` ASC LIMIT 999999 \n" +
            "\t) AS `paperrecord`\n" +
            "\tLEFT OUTER JOIN `certificate` AS `certificate` ON `paperrecord`.`id` = `certificate`.`recordId` \n" +
            "\tAND `certificate`.`isDelete` = 0 \n" +
            "\tAND `certificate`.`belongTo` = 0 ${ew.customSqlSegment}")
    public List<CertificatesRecordListBo> recordListClass(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT COUNT(`paperrecord`.`id`)\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`paperrecord`.`id`,\n" +
            "\t\t`paperrecord`.`theoryScore`,\n" +
            "\t\t`paperrecord`.`operationScore`,\n" +
            "\t\t`paperrecord`.`averageScore`,\n" +
            "\t\t`paperrecord`.`passed`,\n" +
            "\t\t`paperrecord`.`createTime`,\n" +
            "\t\t`paperrecord`.`resetted`,\n" +
            "\t\t`owner`.`id` AS `ownerId`,\n" +
            "\t\t`owner`.`name` AS `ownerName`,\n" +
            "\t\t`owner`.`realname` AS `ownerRealname`,\n" +
            "\t\t`owner`.`gender` AS `ownerGender`,\n" +
            "\t\t`owner`.`account` AS `ownerAccount`,\n" +
            "\t\t`owner`.`idCardNum` AS `ownerIdCardNum`,\n" +
            "\t\t`owner`.`instituteName` AS `ownerInstituteName`,\n" +
            "\t\t`owner`.`telephone` AS `ownerTelephone`,\n" +
            "\t\t`course`.`id` AS `courseIds`,\n" +
            "\t\t`course`.`title` AS `courseTitle`,\n" +
            "\t\t`course`.`certTemplate` AS `certTemplate`,\n" +
            "\t\t`course`.`setWidth` AS `setWidth`,\n" +
            "\t\t`course`.`setHeight` AS `setHeight`,\n" +
            "\t\t`course`.`coordinate` AS `courseCoordinate` \n" +
            "\tFROM\n" +
            "\t\t`paperrecord` AS `paperrecord`\n" +
            "\t\tINNER JOIN `user` AS `owner` ON `paperrecord`.`userId` = `owner`.`id`\n" +
            "\t\tLEFT OUTER JOIN `course` AS `course` ON `paperrecord`.`courseId` = `course`.`id`  \n" +
            "\tORDER BY\n" +
            "\t\t`paperrecord`.`createTime` ASC,\n" +
            "\t\t`paperrecord`.`id` ASC \n" +
            "\t) AS `paperrecord`\n" +
            "\tLEFT OUTER JOIN `certificate` AS `certificate` ON `paperrecord`.`id` = `certificate`.`recordId` \n" +
            "\tAND `certificate`.`isDelete` = 0 \n" +
            "\tAND `certificate`.`belongTo` = 0\n" +
            "\tINNER JOIN `chapter` AS `course->chapters` ON `courseIds` = `course->chapters`.`courseId`\n" +
            "\tINNER JOIN `task` AS `course->chapters->tasks` ON `course->chapters`.`id` = `course->chapters->tasks`.`chapterId` \n" +
            "\tAND `course->chapters->tasks`.`type` = 3 ${ew.customSqlSegment}")
    public Integer recordListNumCourse(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    @Select("SELECT COUNT(`paperrecord`.`id`)\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`paperrecord`.`id`,\n" +
            "\t\t`paperrecord`.`theoryScore`,\n" +
            "\t\t`paperrecord`.`operationScore`,\n" +
            "\t\t`paperrecord`.`averageScore`,\n" +
            "\t\t`paperrecord`.`passed`,\n" +
            "\t\t`paperrecord`.`createTime`,\n" +
            "\t\t`paperrecord`.`resetted`,\n" +
            "\t\t`owner`.`id` AS `ownerId`,\n" +
            "\t\t`owner`.`name` AS `ownerName`,\n" +
            "\t\t`owner`.`realname` AS `ownerRealname`,\n" +
            "\t\t`owner`.`gender` AS `ownerGender`,\n" +
            "\t\t`owner`.`account` AS `ownerAccount`,\n" +
            "\t\t`owner`.`idCardNum` AS `ownerIdCardNum`,\n" +
            "\t\t`owner`.`instituteName` AS `ownerInstituteName`,\n" +
            "\t\t`owner`.`telephone` AS `ownerTelephone`,\n" +
            "\t\t`course`.`id` AS `courseIds`,\n" +
            "\t\t`course`.`title` AS `courseTitle`,\n" +
            "\t\t`course`.`certTemplate` AS `certTemplate`,\n" +
            "\t\t`course`.`setWidth` AS `setWidth`,\n" +
            "\t\t`course`.`setHeight` AS `setHeight`,\n" +
            "\t\t`course`.`coordinate` AS `courseCoordinate` \n" +
            "\tFROM\n" +
            "\t\t`paperrecord` AS `paperrecord`\n" +
            "\t\tINNER JOIN `user` AS `owner` ON `paperrecord`.`userId` = `owner`.`id`\n" +
            "\t\tLEFT OUTER JOIN `course` AS `course` ON `paperrecord`.`courseId` = `course`.`id`  \n" +
            "\tORDER BY\n" +
            "\t\t`paperrecord`.`createTime` ASC,\n" +
            "\t\t`paperrecord`.`id` ASC \n" +
            "\t) AS `paperrecord`\n" +
            "\tLEFT OUTER JOIN `certificate` AS `certificate` ON `paperrecord`.`id` = `certificate`.`recordId` \n" +
            "\tAND `certificate`.`isDelete` = 0 \n" +
            "\tAND `certificate`.`belongTo` = 0\n" +
            "\tINNER JOIN `chapter` AS `course->chapters` ON `courseIds` = `course->chapters`.`courseId`\n" +
            "\tINNER JOIN `task` AS `course->chapters->tasks` ON `course->chapters`.`id` = `course->chapters->tasks`.`chapterId` \n" +
            "\tAND `course->chapters->tasks`.`type` = 3 ${ew.customSqlSegment}")
    public Integer recordListNumClass(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

}
